package tool.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtil {

	private static final String DB_FILE = "jlt.db";

	public Connection getConnection() throws ClassNotFoundException, SQLException {
		Connection c = null;
		Class.forName("org.sqlite.JDBC");
		c = DriverManager.getConnection("jdbc:sqlite:" + DB_FILE);
		return c;
	}

	public int update(String sql) {
		try (Connection connection = getConnection()) {
			try (Statement stmt = connection.createStatement()) {
				int res = stmt.executeUpdate(sql);
				return res;
			}
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}

	public boolean createDefTable() {
		String ddl = "create table filter(" + 
				" id integer PRIMARY KEY AUTOINCREMENT," + 
				" name varchar(500)," + 
				" type varchar(10)," + 
				" scene int" + 
				")" ;
		try (Connection connection = getConnection()) {
			try (Statement stmt = connection.createStatement()) {
				boolean res = stmt.execute(ddl);
				return res;
			}
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	public ResultSet query(String sql) {
		try (Connection connection = getConnection()) {
			try (Statement stmt = connection.createStatement()) {
				ResultSet res = stmt.executeQuery(sql);
				return res;
			}
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public List<Map<String, Object>> queryForList(String sql) {
		try (Connection connection = getConnection()) {
			try (Statement stmt = connection.createStatement()) {
				try (ResultSet res = stmt.executeQuery(sql)) {
					if (res != null) {
						List<Map<String, Object>> rsm = new ArrayList<>();
						while (res.next()) {
							int columnCount = res.getMetaData().getColumnCount();
							Map<String, Object> map = new HashMap<>();
							for (int i = 1; i <= columnCount; i++) {
								Object object = res.getObject(i);
								String columnName = res.getMetaData().getColumnName(i);
								map.put(columnName, object);
							}
							rsm.add(map);
						}
						return rsm;
					}else {
						return new ArrayList<>();
					}
				}
			}
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
}
